package com.mymaxis.manager;

import java.text.*;
import java.util.*;
import java.util.Date;
import java.sql.*;

import com.checkfree.isolutions.property.Properties;
import com.checkfree.isolutions.property.PropertiesConfig;
import com.myebills.ebpsfrontend.controller.CusConnectionPoolServlet;
import com.myebills.ebpsfrontend.controller.EbppxBillDeliveryDataBean;
import com.mymaxis.ebppx.EbppxAcctDetailsDataBean;
import com.mymaxis.ebppx.EbppxConstants;
import com.mymaxis.utils.DateUtil;

/**
 * Module Name  : EbppxCusDBUtil
 * Description  : DB utilities.
 * Last Updated : 2002-11-26
 *
 * @author
 *
 */

public class EbppxCusDBUtil {

	private static boolean connectionPoolOn = false;

    public static Connection getDbConnection() throws Exception {

        Connection conn=null;

        try {

			com.checkfree.isolutions.property.Properties appCfg = com.checkfree.isolutions.property.PropertiesConfig.getApplicationSettings();

			if (appCfg.hasProperty("CusConnectionPool") == false)
				connectionPoolOn = false;
			else {
				String temp = appCfg.getProperty("CusConnectionPool");
				if (temp.compareToIgnoreCase("Yes")==0){
					connectionPoolOn = true;
				}
			}

			if (connectionPoolOn){
                conn = CusConnectionPoolServlet.connectionPool.getConnection();
                System.out.println(CusConnectionPoolServlet.connectionPool.toString());
                System.out.println("--- Connection opened ---");
			} else {
				String jdbcDriver = new String();
				String jdbcDataSource = new String();
				String username = new String();
				String password = new String();

				if (appCfg.hasProperty("JDBCDriverName") == false)
					throw new IllegalArgumentException("JDBC Driver not specified!");
				else
					jdbcDriver = appCfg.getProperty("JDBCDriverName");

				if (appCfg.hasProperty("JDBCDataSourceName") == false)
					throw new IllegalArgumentException("JDBC Data Source not specified!");
				else
					jdbcDataSource = appCfg.getProperty("JDBCDataSourceName");

				if (appCfg.hasProperty("DataBaseUserid") == false)
					throw new IllegalArgumentException("Database userid not specified!");
				else
					username = appCfg.getProperty("DataBaseUserid");

				if (appCfg.hasProperty("DataBasePassword") == false)
					throw new IllegalArgumentException("Database password not specified!");
				else
					password = appCfg.getProperty("DataBasePassword");

				Class.forName(jdbcDriver);
				conn = DriverManager.getConnection(jdbcDataSource, username, password);
				System.out.println("--- Connection opened ---");
			}
        }
        catch(Exception e) {
			if (connectionPoolOn) {
            	throw new Exception("Error getting DB connection. Connection pool servlet should be running: " + e);
			}
			else {
            	throw new Exception("Error getting DB connection: " + e);
			}
        }

        return conn;
    }

/**********************************************************************************************/

    public static Connection getKenanDbConnection() throws Exception {

        Connection conn=null;

        try {

			com.checkfree.isolutions.property.Properties appCfg = com.checkfree.isolutions.property.PropertiesConfig.getApplicationSettings();

			String jdbcDriver = new String();
			String jdbcDataSource = new String();
			String username = new String();
			String password = new String();
            
			if (appCfg.hasProperty("KenanJDBCDriverName") == false)
				throw new IllegalArgumentException("JDBC Driver not specified!");
			else
				jdbcDriver = appCfg.getProperty("KenanJDBCDriverName");

			if (appCfg.hasProperty("KenanJDBCDataSourceName") == false)
				throw new IllegalArgumentException("JDBC Data Source not specified!");
			else
				jdbcDataSource = appCfg.getProperty("KenanJDBCDataSourceName");

			if (appCfg.hasProperty("KenanDataBaseUserid") == false)
				throw new IllegalArgumentException("Database userid not specified!");
			else
				username = appCfg.getProperty("KenanDataBaseUserid");

			if (appCfg.hasProperty("KenanDataBasePassword") == false)
				throw new IllegalArgumentException("Database password not specified!");
			else
				password = appCfg.getProperty("KenanDataBasePassword");

			Class.forName(jdbcDriver);
			conn = DriverManager.getConnection(jdbcDataSource, username, password);
			System.out.println("--- Connection opened ---");
        }
        catch(Exception e) {
           	throw new Exception("Error getting DB connection: " + e);
        }

        return conn;
    }

    public static void freeKenanDbConnection(Connection conn) throws Exception {
        try {
			if (conn != null){
				conn.close();
				System.out.println("--- Connection closed ---");
			} else {
				System.out.println("--- Connection is null ---");
			}
        }
        catch(Exception e) {
            throw new Exception("Error closing DB connection : " + e);
        }
   }
/************************************************************************************************/
    public static void freeDbConnection(Connection conn) throws Exception {
        try {
			if (connectionPoolOn){
				CusConnectionPoolServlet.connectionPool.free(conn);
				System.out.println("--- Connection closed ---");
				System.out.println(CusConnectionPoolServlet.connectionPool.toString());
			} else {
				if (conn != null){
					conn.close();
					System.out.println("--- Connection closed ---");
				} else {
					System.out.println("--- Connection is null ---");
				}
			}
        }
        catch(Exception e) {
            throw new Exception("Error closing DB connection : " + e);
        }
   }

    /**
     *
     *
     *
     */
/*********************************** Added by Meau ****************************************/
    public static Vector processCUSSubscriptionReportData(String acctId, String oldAcctId)throws Exception {

        String statusActivityCodeForSubscription = "AC009";

        Connection connection = null;
        PreparedStatement pstmt = null;
        ResultSet rs= null;
        StringBuffer query = new StringBuffer();
        Vector vecData = new Vector();
        final DateFormat df = new SimpleDateFormat(EbppxConstants.DATE_FORMAT);

        try{
			connection = getDbConnection();
            if (connection!=null){

				//search for status records first
				query.append("SELECT /*+ index(custlog ix3_custlog)*/ DISTINCT(DATE2), STATUS, USERFIELD1, USERFIELD4 FROM CUSTLOG WHERE ");
				query.append("ACCTID in ( ? , ? ) and DATE2 >=sysdate-180 AND ACTIVITYCODE = ?  ORDER BY DATE2 DESC");

				// prepare the statement and bind parameter
				pstmt = connection.prepareStatement(query.toString());
				pstmt.setString(1,acctId);
				pstmt.setString(2,oldAcctId);
				pstmt.setString(3,statusActivityCodeForSubscription);

				rs   = pstmt.executeQuery();

				while(rs.next()){

					java.sql.Timestamp statementDate = rs.getTimestamp(1);
					String recordStatus = rs.getString(2);
					String emailAdd = rs.getString(3);
					String timeSent = getTimeSentToExpedite(rs.getString(4));

                    EbppxBillDeliveryDataBean data = new EbppxBillDeliveryDataBean();

                    data.setStmtDate(DateUtil.formatDate(statementDate,EbppxConstants.DATE_FORMAT));
                    data.setTimeSent(timeSent);
                    data.setStatus(recordStatus);
                    data.setEmailAdd(emailAdd);
                    data.setActualStmtDate(statementDate);

					vecData.addElement(data);
				}

				if (pstmt != null) {
					pstmt.close();
				}
				if(rs!=null){
					rs=null;
				}
			}
			System.out.println("Bill delivery report has been successfully executed");
		}catch(Exception e){
			throw new Exception("" + e);
		}finally{
			if(connection!=null){
                EbppxCusDBUtil.freeDbConnection(connection);
            }
			if(rs!=null){
				rs=null;
			}
		}
		return vecData;
	}


  private static String getTimeSentToExpedite(String str) {
    int startIdx = str.indexOf(">>");
    int endIdx = str.indexOf("|", startIdx);
    if ((startIdx < 0) || (endIdx < 0) || (endIdx < startIdx)) {
      return "";
    }
    return str.substring(startIdx+2, endIdx);
  }


///////////////////////////////////////////////////////////////////////////////////////////////
    public static String processCUSPhysicalBillSubscription(String extId,String cusDatabase,String categoryDatabase)throws Exception {

        Connection connection = null;
        PreparedStatement pstmt = null;
        ResultSet rs= null;
        StringBuffer query = new StringBuffer();
		String strPhysicalBillSubscription = "";
		int paramId = 19;
		int paramDataType = 2;

        try{
            connection = getKenanDbConnection();
            if (connection!=null){

                if (connection!=null){

                    //search for status records first
	                query.append("SELECT PARAM_VALUE FROM CMF_EXT_DATA@"+cusDatabase+" WHERE ACCOUNT_NO = (SELECT ACCOUNT_NO FROM CUSTOMER_ID_ACCT_MAP@"+cusDatabase+" WHERE EXTERNAL_ID_TYPE=1 AND EXTERNAL_ID='"+extId+"') AND PARAM_ID = "+paramId+" AND PARAM_DATATYPE = "+paramDataType+" ");

                    // prepare the statement and bind parameter
                    pstmt = connection.prepareStatement(query.toString());

                    System.out.println(query.toString());

                    rs   = pstmt.executeQuery();

					if(rs.next()){
						strPhysicalBillSubscription = rs.getString(1);
					}

                    if (pstmt != null) {
                        pstmt.close();
                    }
                }

            }
            System.out.println("Process Physical Bill Subscription has been successfully retrieved");
        }catch(Exception e){
            throw new Exception("" + e);
        }finally{
            if(connection!=null){
                EbppxCusDBUtil.freeKenanDbConnection(connection);
            }
            if(rs!=null){
                rs=null;
            }
        }
        return strPhysicalBillSubscription;
    }

    public static String processCUSPhysicalBillSubscription(Connection connection,String cusDatabase,String extId,String categoryDatabase)throws Exception {

        PreparedStatement pstmt = null;
        ResultSet rs= null;
        StringBuffer query = new StringBuffer();
		String strPhysicalBillSubscription = "";
		int paramId = 19;
		int paramDataType = 2;

        try{
			if (connection!=null){
				//search for status records first
                query.append("SELECT PARAM_VALUE FROM CMF_EXT_DATA@"+cusDatabase+" WHERE ACCOUNT_NO = (SELECT ACCOUNT_NO FROM CUSTOMER_ID_ACCT_MAP@"+cusDatabase+" WHERE EXTERNAL_ID_TYPE=1 AND EXTERNAL_ID='"+extId+"') AND PARAM_ID = "+paramId+" AND PARAM_DATATYPE = "+paramDataType+" ");
                

				// prepare the statement and bind parameter
				pstmt = connection.prepareStatement(query.toString());
                rs   = pstmt.executeQuery();

				if(rs.next()){
					strPhysicalBillSubscription = rs.getString(1);
				}

				if (pstmt != null) {
					pstmt.close();
				}
			}
            System.out.println("Process Physical Bill Subscription with connection has been successfully retrieved");
        }catch(Exception e){
            throw new Exception("" + e);
        }finally{
            if(rs!=null){
                rs=null;
            }
        }
        return strPhysicalBillSubscription;
    }

////////////////////////////////////////////////////////////////////////////////////////////////
    public static String getCategoryDatabase(Connection connection)throws Exception {

        PreparedStatement pstmt = null;
        ResultSet rs= null;
        StringBuffer query = new StringBuffer();
		String strCategoryDatabase = "";

        try{
			if (connection!=null){
				//search for status records first
                query.append("SELECT DS_DATABASE FROM SERVER_DEFINITION WHERE SERVER_TYPE=1");

				// prepare the statement and bind parameter
				pstmt = connection.prepareStatement(query.toString());
                rs   = pstmt.executeQuery();

				if(rs.next()){
					strCategoryDatabase = rs.getString(1);
				}

				if (pstmt != null) {
					pstmt.close();
				}
			}
            System.out.println("Select category database name with connection has been successfully retrieved");
        }catch(Exception e){
            throw new Exception("" + e);
        }finally{
            if(rs!=null){
                rs=null;
            }
        }
        return strCategoryDatabase;
    }

////////////////////////////////////////////////////////////////////////////////////////////////
    public static String getCategoryDatabase()throws Exception {

        Connection connection = null;
        PreparedStatement pstmt = null;
        ResultSet rs= null;
        StringBuffer query = new StringBuffer();
		String strCategoryDatabase = "";

        try{
			connection = getKenanDbConnection();
			if (connection!=null){
				//search for status records first
                query.append("SELECT DS_DATABASE FROM SERVER_DEFINITION WHERE SERVER_CATEGORY=1 AND SERVER_TYPE=1");
				System.out.println(query.toString());

				// prepare the statement and bind parameter
				pstmt = connection.prepareStatement(query.toString());

                rs   = pstmt.executeQuery();

				if(rs.next()){
					strCategoryDatabase = rs.getString(1);
				}

				if (pstmt != null) {
					pstmt.close();
				}
			}
            System.out.println("Select category database name has been successfully retrieved");
        }catch(Exception e){
            throw new Exception("" + e);
        }finally{
            if(connection!=null){
                EbppxCusDBUtil.freeKenanDbConnection(connection);
            }
            if(rs!=null){
                rs=null;
            }
        }

        return strCategoryDatabase;
    }

////////////////////////////////////////////////////////////////////////////////////////////////
    public static String getCusDatabase(Connection connection,String categoryDatabase,String externalId)throws Exception {

        PreparedStatement pstmt = null;
        ResultSet rs= null;
        StringBuffer query = new StringBuffer();
		String strCusDatabase = "";

        try{
			if (connection!=null){
				//search for status records first
                query.append("SELECT DS_DATABASE FROM EXTERNAL_ID_ACCT_MAP@"+categoryDatabase+" EIAM,SERVER_DEFINITION@"+categoryDatabase+" SD WHERE EXTERNAL_ID='"+externalId+"' AND EXTERNAL_ID_TYPE=1 AND EIAM.SERVER_ID=SD.SERVER_ID");

				// prepare the statement and bind parameter
				pstmt = connection.prepareStatement(query.toString());
                System.out.println("SQL string is....1"+query.toString());
                rs   = pstmt.executeQuery();
                System.out.println("SQL string is....4"+query.toString());


				if(rs.next()){
					strCusDatabase = rs.getString(1);
				}

				if (pstmt != null) {
					pstmt.close();
				}
			}
            System.out.println("Select customer database name with connection has been successfully retrieved");
        }catch(Exception e){
            throw new Exception("" + e);
        }finally{
            if(rs!=null){
                rs=null;
            }
        }
        return strCusDatabase;
    }

////////////////////////////////////////////////////////////////////////////////////////////////
    public static String getCusDatabase(String categoryDatabase,String externalId)throws Exception {

        Connection connection = null;
        PreparedStatement pstmt = null;
        ResultSet rs= null;
        StringBuffer query = new StringBuffer();
		String strCusDatabase = "";

        try{
			connection = getKenanDbConnection();
			if (connection!=null){
				//search for status records first
                query.append("SELECT DS_DATABASE FROM EXTERNAL_ID_ACCT_MAP@"+categoryDatabase+" EIAM,SERVER_DEFINITION@"+categoryDatabase+" SD WHERE EXTERNAL_ID='"+externalId+"' AND EXTERNAL_ID_TYPE=1 AND EIAM.SERVER_ID=SD.SERVER_ID");

				// prepare the statement and bind parameter
				pstmt = connection.prepareStatement(query.toString());
                rs   = pstmt.executeQuery();

				if(rs.next()){
					strCusDatabase = rs.getString(1);
				}

				if (pstmt != null) {
					pstmt.close();
				}
			}
            System.out.println("Select customer database name with connection has been successfully retrieved");
        }catch(Exception e){
            throw new Exception("" + e);
        }finally{
            if(connection!=null){
                EbppxCusDBUtil.freeKenanDbConnection(connection);
            }
            if(rs!=null){
                rs=null;
            }
        }
        return strCusDatabase;
    }

////////////////////////////////////////////////////////////////////////////////////////////////
    public static String getKenanAccountNo(Connection connection,String categoryDatabase,String externalId)throws Exception {

        PreparedStatement pstmt = null;
        ResultSet rs= null;
        StringBuffer query = new StringBuffer();
		String kenanAccountNo = "";

        try{
			if (connection!=null){
				//search for status records first
                query.append("SELECT ACCOUNT_NO FROM EXTERNAL_ID_ACCT_MAP@"+categoryDatabase+" WHERE EXTERNAL_ID_TYPE=1 AND EXTERNAL_ID='"+externalId+"'");

				// prepare the statement and bind parameter
				pstmt = connection.prepareStatement(query.toString());
				System.out.println(query.toString());
                rs   = pstmt.executeQuery();

				if(rs.next()){
					kenanAccountNo = rs.getString(1);
				}

				if (pstmt != null) {
					pstmt.close();
				}
			}
            System.out.println("Select kenan account no with connection has been successfully retrieved");
        }catch(Exception e){
            throw new Exception("" + e);
        }finally{
            if(rs!=null){
                rs=null;
            }
        }
        return kenanAccountNo;
    }
    public static String getKenanAccountNo(String categoryDatabase,String externalId)throws Exception {

        Connection connection = null;
        PreparedStatement pstmt = null;
        ResultSet rs= null;
        StringBuffer query = new StringBuffer();
		String kenanAccountNo = "";
		connection = getKenanDbConnection();

        try{
			if (connection!=null){
				//search for status records first
                query.append("SELECT ACCOUNT_NO FROM EXTERNAL_ID_ACCT_MAP@"+categoryDatabase+" WHERE EXTERNAL_ID_TYPE=1 AND EXTERNAL_ID='"+externalId+"'");

				// prepare the statement and bind parameter
				pstmt = connection.prepareStatement(query.toString());
				System.out.println(query.toString());
                rs   = pstmt.executeQuery();

				if(rs.next()){
					kenanAccountNo = rs.getString(1);
				}

				if (pstmt != null) {
					pstmt.close();
				}
			}
            System.out.println("Select kenan account no with connection has been successfully retrieved");
        }catch(Exception e){
            throw new Exception("" + e);
        }finally{
			if(connection!=null){
                EbppxCusDBUtil.freeKenanDbConnection(connection);
            }
            if(rs!=null){
                rs=null;
            }
        }
        return kenanAccountNo;
    }
///////////////////////////////////////////////////////////////////////////////////////////////
    public static boolean checkIfPhysicalBillSubsExist(Connection connection,String cusDatabase,String extId,String categoryDatabase)throws Exception {

        PreparedStatement pstmt = null;
        ResultSet rs= null;
        StringBuffer query = new StringBuffer();
        int intResult = 0;
		boolean boolResult = false;
		int paramId = 19;
		int paramDataType = 2;

        try{
            if (connection!=null){

                if (connection!=null){

                    //search for status records first
                    query.append("SELECT COUNT(ACCOUNT_NO) FROM CMF_EXT_DATA@"+cusDatabase+" WHERE ACCOUNT_NO =  (SELECT ACCOUNT_NO FROM CUSTOMER_ID_ACCT_MAP@"+cusDatabase+" WHERE EXTERNAL_ID_TYPE=1 AND EXTERNAL_ID='"+extId+"') AND PARAM_ID ="+paramId+" AND PARAM_DATATYPE ="+paramDataType);

                    // prepare the statement and bind parameter
                    pstmt = connection.prepareStatement(query.toString());
				System.out.println(query.toString());

                    rs   = pstmt.executeQuery();

					if(rs.next()){
						intResult = rs.getInt(1);
					}
					if(intResult != 0){
						boolResult = true;
					}

                    if (pstmt != null) {
                        pstmt.close();
                    }
                }

            }
            System.out.println("Check if the record exist in CMF_EXT_DATA has successfully been executed");
        }catch(Exception e){
            throw new Exception("" + e);
        }finally{
            //if(connection!=null){
            //    EbppxCusDBUtil.freeDbConnection(connection);
            //}
            if(rs!=null){
                rs=null;
            }
        }
        return boolResult;
    }

///////////////////////////////////////////////////////////////////////////////////////////////
    public static boolean authenticateCorpUser(String strUserName,String strPassword) throws Exception{

		Connection connection = null;
        PreparedStatement pstmt = null;
        ResultSet rs= null;
        StringBuffer query = new StringBuffer();
        int intResult = 0;
		boolean boolResult = false;

        try{
			connection = getDbConnection();
            if (connection!=null){
				//search for status records first
                query.append("SELECT COUNT(*) FROM CORPLOGIN WHERE USERID = '"+strUserName+"' AND PASSWORD ='"+strPassword+"'");

                // prepare the statement and bind parameter
                pstmt = connection.prepareStatement(query.toString());
				System.out.println(query.toString());

                rs = pstmt.executeQuery();

				if(rs.next()){
					intResult = rs.getInt(1);
				}
				if(intResult != 0){
					boolResult = true;
				}

                if (pstmt != null) {
                    pstmt.close();
                }

            }
            System.out.println("Check if the record exist in corporate login has successfully been executed");
        }catch(Exception e){
            throw new Exception("" + e);
        }finally{
			try{
        	    if(connection!=null){
        	        EbppxCusDBUtil.freeDbConnection(connection);
        	    }
			}catch(Exception e){
            	//throw new Exception("" + e);
        	}
            if(rs!=null){
                rs=null;
            }
        }
        return boolResult;
    }

    public static boolean checkIfEbillSubsExist(Connection connection,String cusDatabase,String extId,String categoryDatabase)throws Exception {

        PreparedStatement pstmt = null;
        ResultSet rs= null;
        StringBuffer query = new StringBuffer();
        int intResult = 0;
		boolean boolResult = false;
		int paramId = 17;
		int paramDataType = 2;

        try{
            if (connection!=null){

                if (connection!=null){

                    //search for status records first
                    query.append("SELECT COUNT(ACCOUNT_NO) FROM CMF_EXT_DATA@"+cusDatabase+" WHERE ACCOUNT_NO =  (SELECT ACCOUNT_NO FROM CUSTOMER_ID_ACCT_MAP@"+cusDatabase+" WHERE EXTERNAL_ID_TYPE=1 AND EXTERNAL_ID='"+extId+"') AND PARAM_ID = " +paramId+" AND PARAM_DATATYPE = "+paramDataType);

                    // prepare the statement and bind parameter
                    pstmt = connection.prepareStatement(query.toString());
				System.out.println(query.toString());

                    rs   = pstmt.executeQuery();

					if(rs.next()){
						intResult = rs.getInt(1);
					}

					if(intResult != 0){
						boolResult = true;
					}

                    if (pstmt != null) {
                        pstmt.close();
                    }
                }

            }
            System.out.println("Check if the record exist in CMF_EXT_DATA has successfully been executed");
        }catch(Exception e){
            throw new Exception("" + e);
        }finally{
            //if(connection!=null){
            //    EbppxCusDBUtil.freeDbConnection(connection);
            //}
            if(rs!=null){
                rs=null;
            }
        }
        return boolResult;
    }

    public static boolean checkIfStatementExtReady(long instanceId)throws Exception {

        PreparedStatement pstmt = null;
        ResultSet rs= null;
        StringBuffer query = new StringBuffer();
        int intResult = 0;
		boolean boolResult = false;
		Connection connection = getDbConnection();

        try{
            if (connection!=null){


                    //search for status records first
                    query.append("SELECT COUNT(*) FROM STATEMENTEXT WHERE INSTANCEID ='"+instanceId+"'");

                    // prepare the statement and bind parameter
                    pstmt = connection.prepareStatement(query.toString());
					System.out.println(query.toString());

                    rs   = pstmt.executeQuery();

					if(rs.next()){
						intResult = rs.getInt(1);
					}

					if(intResult != 0){
						boolResult = true;
					}

                    if (pstmt != null) {
                        pstmt.close();
                    }

            }
            System.out.println("Check if the record exist in statementext has successfully been executed");
        }catch(Exception e){
            throw new Exception("" + e);
        }finally{
            if(connection!=null){
                EbppxCusDBUtil.freeDbConnection(connection);
            }
            if(rs!=null){
                rs=null;
            }
        }
        System.out.println("Cool Result is ....."+boolResult);
        return boolResult;
    }

    public static void insertInfoCustText(Connection connection, String cusDatabase,String activityCode, String acctId) throws Exception {
        try {

			int intAcctId= Integer.parseInt(acctId);


            if (connection!=null){
				StringBuffer stbQuery = new StringBuffer();
                StringBuffer stbSQL = new StringBuffer();
		        ResultSet rs= null;
                String subscriptionStatus = "";
                int paramDataType = 2;
                int paramId = 0;
        		final DateFormat subsFormatter = new SimpleDateFormat("'Y,'dd/MM/yyyy");
        		final DateFormat unSubsFormatter = new SimpleDateFormat("'N,'dd/MM/yyyy");

            	Date dtUpdateDate = new Date();

				if(activityCode.equals(EbppxConstants.NO_PHYSICAL_BILL_SUBSCRIPTION)){

					// No physical bill subscription
					subscriptionStatus = subsFormatter.format(dtUpdateDate);
					paramId = 19;
					stbSQL.append("INSERT INTO CMF_EXT_DATA@"+cusDatabase+"(ACCOUNT_NO,PARAM_ID,PARAM_VALUE,PARAM_DATATYPE) VALUES ("+intAcctId+","+paramId+",'"+subscriptionStatus+"',"+paramDataType+")");

				}else if(activityCode.equals(EbppxConstants.NO_PHYSICAL_BILL_UNSUBSCRIPTION)){

					// No physical bill unsubscription
					subscriptionStatus = unSubsFormatter.format(dtUpdateDate);
					paramId = 19;
					stbSQL.append("INSERT INTO CMF_EXT_DATA@"+cusDatabase+"(ACCOUNT_NO,PARAM_ID,PARAM_VALUE,PARAM_DATATYPE) VALUES ("+intAcctId+","+paramId+",'"+subscriptionStatus+"',"+paramDataType+")");

				}else if(activityCode.equals(EbppxConstants.EMAIL_DELIVERY_SUBSCRIPTION)){

					// Bill Delivery email subscription
					subscriptionStatus = subsFormatter.format(dtUpdateDate);
					paramId = 17;
					stbSQL.append("INSERT INTO CMF_EXT_DATA@"+cusDatabase+"(ACCOUNT_NO,PARAM_ID,PARAM_VALUE,PARAM_DATATYPE) VALUES ("+intAcctId+","+paramId+",'"+subscriptionStatus+"',"+paramDataType+")");

				}else if(activityCode.equals(EbppxConstants.EMAIL_DELIVERY_UNSUBSCRIPTION)){

					// Bill Delivery Email unsubscription
					subscriptionStatus = unSubsFormatter.format(dtUpdateDate);
					paramId = 17;
					stbSQL.append("INSERT INTO CMF_EXT_DATA@"+cusDatabase+"(ACCOUNT_NO,PARAM_ID,PARAM_VALUE,PARAM_DATATYPE) VALUES ("+intAcctId+","+paramId+",'"+subscriptionStatus+"',"+paramDataType+")");

				}

				PreparedStatement pstmt = connection.prepareStatement(stbSQL.toString());
				System.out.println(stbSQL.toString());
				pstmt.executeUpdate();

				if (pstmt != null) {
					pstmt.close();
				}
            }
        }
        catch(Exception e) {
            throw new Exception("Error inserting CMF_EXT_DATA : " + e);
        }
    }

    public static void updateInfoCustText(Connection connection,String cusDatabase, String activityCode, String acctId) throws Exception {
        try {
            if (connection!=null){
                StringBuffer stbSQL = new StringBuffer();
                String subscriptionStatus = "";
                int paramDataType = 2;
                int paramId = 0;
                int intAcctId = Integer.parseInt(acctId);
        		final DateFormat subsFormatter = new SimpleDateFormat("'Y,'dd/MM/yyyy");
        		final DateFormat unSubsFormatter = new SimpleDateFormat("'N,'dd/MM/yyyy");

            	Date dtUpdateDate = new Date();

            	if(activityCode.equals(EbppxConstants.NO_PHYSICAL_BILL_SUBSCRIPTION)){
					// No physical bill subscription
					subscriptionStatus = subsFormatter.format(dtUpdateDate);
					paramId = 19;
					stbSQL.append("UPDATE CMF_EXT_DATA@"+cusDatabase+" SET PARAM_VALUE = '"+subscriptionStatus+"' WHERE ACCOUNT_NO = "+intAcctId+" AND PARAM_ID = "+paramId+" AND PARAM_DATATYPE = "+paramDataType+" ");
				}else if(activityCode.equals(EbppxConstants.NO_PHYSICAL_BILL_UNSUBSCRIPTION)){
					// No physical bill unsubscription
					subscriptionStatus = unSubsFormatter.format(dtUpdateDate);
					paramId = 19;
					stbSQL.append("UPDATE CMF_EXT_DATA@"+cusDatabase+" SET PARAM_VALUE = '"+subscriptionStatus+"' WHERE ACCOUNT_NO = "+intAcctId+" AND PARAM_ID = "+paramId+" AND PARAM_DATATYPE = "+paramDataType+" ");
				}else if(activityCode.equals(EbppxConstants.EMAIL_DELIVERY_SUBSCRIPTION)){
					// Bill Delivery email subscription
					subscriptionStatus = subsFormatter.format(dtUpdateDate);
					paramId = 17;
					stbSQL.append("UPDATE CMF_EXT_DATA@"+cusDatabase+" SET PARAM_VALUE = '"+subscriptionStatus+"' WHERE ACCOUNT_NO = "+intAcctId+" AND PARAM_ID = "+paramId+" AND PARAM_DATATYPE = "+paramDataType+" ");
				}else if(activityCode.equals(EbppxConstants.EMAIL_DELIVERY_UNSUBSCRIPTION)){
					// Bill Delivery Email unsubscription
					subscriptionStatus = unSubsFormatter.format(dtUpdateDate);
					paramId = 17;
					stbSQL.append("UPDATE CMF_EXT_DATA@"+cusDatabase+" SET PARAM_VALUE = '"+subscriptionStatus+"' WHERE ACCOUNT_NO = "+intAcctId+" AND PARAM_ID = "+paramId+" AND PARAM_DATATYPE = "+paramDataType+" ");
				}

				PreparedStatement pstmt = connection.prepareStatement(stbSQL.toString());
				pstmt.executeUpdate();

				if (pstmt != null) {
					pstmt.close();
				}
            }
        }
        catch(Exception e) {
            throw new Exception("Error updating CMF_EXT_DATA : " + e);
        }
    }


/***************************** Added by Meau for Email delivery view count***************************************************/
    public static boolean checkIfEmailViewed(String zipFile,String accountNo,String stmtDate)throws Exception {

        PreparedStatement pstmt = null;
        ResultSet rs= null;
        StringBuffer query = new StringBuffer();
        int intResult = 0;
		boolean boolResult = false;
		Connection connection = getDbConnection();

        try{
            if (connection!=null){


                    //search for status records first
                    query.append("SELECT COUNT(*) FROM EMAILDELIVERY WHERE ZIPFILE= ? ");
                    query.append(" AND ACCNO=? AND STMTDATE= ? ");

                    // prepare the statement and bind parameter
                    pstmt = connection.prepareStatement(query.toString());
					System.out.println(query.toString());

					pstmt.setString(1,zipFile);
					pstmt.setString(2,accountNo);
					pstmt.setString(3,stmtDate);
 
                    rs   = pstmt.executeQuery();

					if(rs.next()){
						intResult = rs.getInt(1);
					}

					if(intResult != 0){
						boolResult = true;
					}

		            if(rs!=null){
						rs.close();
					}

                    if (pstmt != null) {
                        pstmt.close();
                    }

            }
            System.out.println("Check if the record exist in statementext has successfully been executed");
        }catch(Exception e){
            throw new Exception("" + e);
        }finally{
            if(connection!=null){
                EbppxCusDBUtil.freeDbConnection(connection);
            }
            if(rs!=null){
                rs=null;
            }
        }
        System.out.println("bool Result is ....."+boolResult);
        return boolResult;
    }

    public static void updateViewCount(String zipFile,String accountNo,String stmtDate) throws Exception {

		Connection connection = getDbConnection();
        try {
            if (connection!=null && !checkIfEmailViewed(zipFile,accountNo,stmtDate)){

				java.util.Date openDate = new java.util.Date();

                PreparedStatement pstmt = connection.prepareStatement("INSERT INTO EMAILDELIVERY (ZIPFILE, ACCNO, STMTDATE, DATEOPEN) VALUES (?,?,?,?)");
                pstmt.setString(1,zipFile);
                pstmt.setString(2,accountNo);
                pstmt.setString(3,stmtDate);
				pstmt.setTimestamp(4,new java.sql.Timestamp(openDate.getTime()));
                pstmt.execute();

				if (pstmt != null) {
					pstmt.close();
				}

            }
        }
        catch(Exception e) {
            throw new Exception("Error inserting to email delivery table : " + e);
        }finally{
            if(connection!=null){
                EbppxCusDBUtil.freeDbConnection(connection);
            }
        }
    }

/*****************************end added by Meau***************************************************/


    public static void logActivity(Connection connection, Statement stmt, String activityCode, String acctId, String userId, String role, String status, String date1, String date2, String userField1, String userField2, String userField3, String userField4) throws Exception {
        try {
            if (connection!=null && stmt!=null){

                StringBuffer stbSQL = new StringBuffer();

                final DateFormat df = new SimpleDateFormat(EbppxConstants.DATE_FORMAT+" "+EbppxConstants.TIME_FORMAT);

                java.sql.Timestamp ts1 = null;
                java.sql.Timestamp ts2 = null;

                if (!date1.equals("")){
                    ts1 = new Timestamp((df.parse(date1)).getTime());
                }
                if (!date2.equals("")){
                    ts2 = new Timestamp((df.parse(date2)).getTime());
                }

                PreparedStatement pstmt = connection.prepareStatement("INSERT INTO CUSTLOG (activityCode, acctId, userId, role, status, date1, date2, userField1, userField2, userField3, userField4) VALUES (?,?,?,?,?,?,?,?,?,?,?)");
                pstmt.setString(1,activityCode);
                pstmt.setString(2,acctId);
                pstmt.setString(3,userId);
                pstmt.setString(4,role);
                pstmt.setString(5,status);
                pstmt.setTimestamp(6,ts1);
                pstmt.setTimestamp(7,ts2);
                pstmt.setString(8,userField1);
                pstmt.setString(9,userField2);
                pstmt.setString(10,userField3);
                pstmt.setString(11,userField4);
                pstmt.execute();

				if (pstmt != null) {
					pstmt.close();
				}

            }
        }
        catch(Exception e) {
            throw new Exception("Error logging user activity : " + e);
        }
    }

    public static void logUserActivity(String activityCode, String acctId, String userId, String role, String status, String date1, String date2, String userField1, String userField2, String userField3, String userField4) throws Exception {
        Connection connection = null;
        Statement stmt = null;
        try {
            connection = getDbConnection();
            if (connection!=null){
                stmt = connection.createStatement();

                if (stmt!=null){
					connection.setAutoCommit(false);
                    logActivity(connection, stmt, activityCode, acctId, userId, role, status, date1, date2, userField1, userField2, userField3, userField4);
	                connection.commit();
					connection.setAutoCommit(true);
                }

                System.out.println("Transaction has been committed");

            }
        }
        catch(Exception e) {
            throw new Exception("" + e);
        }finally {
			if (stmt!=null){
				stmt.close();
			}
			if (connection!=null){
				EbppxCusDBUtil.freeDbConnection(connection);
			}
		}
    }
    

    //=================added by tzer kang
    public static EbppxAcctDetailsDataBean getLatestEmailDeliveryStatus(Connection connection,String acctId){
    	 
    	ResultSet rs= null;
    	EbppxAcctDetailsDataBean acctDataBean = new EbppxAcctDetailsDataBean();
    	String registerStatus ="";
    	String emailAddress ="";
    	String emailStatus = "";
    	String channelID = "";
    	String userID = "";
    	Date registerDate = null;
      
    	List status =new ArrayList();
 
    	try{
    		if(connection != null){
    			
    			  PreparedStatement pstmt = connection.prepareStatement("SELECT TRIM(ACCTID),TRIM(EMAILADDR),DATE1,TRIM(USERID),TRIM(USERFIELD1), TRIM(USERFIELD2),TRIM(CHANNELID) FROM EBILLREGISTERLOG WHERE ACCTID=? ORDER BY DATE1 DESC ");
    			   

                   
                   pstmt.setString(1,acctId);
                   pstmt.setMaxRows(1);
                   rs   = pstmt.executeQuery();
                   while(rs.next()){
                	   
				    registerStatus = rs.getString(5);
				    emailAddress =rs.getString(2);
				    emailStatus = rs.getString(6);
				    registerDate = rs.getTimestamp(3);
				    userID = rs.getString(4);
				    channelID = rs.getString(7);
 			        System.out.println("registerStatus :"+registerStatus);
				    acctDataBean.setEmailAdd(emailAddress);
				    acctDataBean.setEmailStatus(registerStatus);
				    acctDataBean.setRegisterDate(registerDate);
				    acctDataBean.setLastModifyDate(registerDate);
				    acctDataBean.setUpdatedByChannel(channelID);
				    acctDataBean.setUpdatedByUser(userID);
		
				    
                   }
				    		
				   System.out.println("registerDate :"+acctDataBean.getRegisterDate());
				    
				    
				
				 System.out.println("registerStatus :"+registerStatus);
				 System.out.println("Email Address :"+acctDataBean.getEmailAdd());
					

                   if (pstmt != null) {
                       pstmt.close();
                   }
                }
    		}catch(Exception e){
            	   e.printStackTrace();
    		}
    		return acctDataBean;
 }
  


    		
    	
    	
    	
    
  
    public static void logEmailDelivery(Connection connection,String acctId, String emailAddress, String date, String userId, String userField1, String userField2,String channelID) throws Exception {
        try {
            if (connection!=null ){

                StringBuffer stbSQL = new StringBuffer();

                final DateFormat df = new SimpleDateFormat(EbppxConstants.DATE_FORMAT+" "+EbppxConstants.TIME_FORMAT);

                java.sql.Timestamp ts1 = null;
                java.sql.Timestamp ts2 = null;

                if (!date.equals("")){
                    ts1 = new Timestamp((df.parse(date)).getTime());
              

                    PreparedStatement pstmt = connection.prepareStatement("INSERT INTO EBILLREGISTERLOG (ACCTID,EMAILADDR,DATE1,USERID,USERFIELD1, USERFIELD2, CHANNELID) VALUES (?,?,?,?,?,?,?)");
                    pstmt.setString(1,acctId);
                    pstmt.setString(2,emailAddress);
                    pstmt.setTimestamp(3,ts1);
                    pstmt.setString(4,userId);
                    pstmt.setString(5,userField1);
                    pstmt.setString(6,userField2);
                    pstmt.setString(7,channelID);
                  
                   
                pstmt.execute();

  				if (pstmt != null) {
  					pstmt.close();
  				}

            }
            }
        }
        catch(Exception e) {
            throw new Exception("Error logging user activity : " + e);
        }
    
    }
    // ===END ADDED BY TZER KANG
  }

